package cs342.group1.messenger;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;


import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.EditText;

public class Database {
	public static final String DATABASE_NAME = "MyMessaging";
	public static final int SCHEMA_VERSION = 1;
	
	//Database names and columns for user table 
	public static final String TABLE_USER = "User";
	public static final String USER_ID = "Id";
	public static final String CLOUD_ID = "CloudId";
	public static final String USERNAME = "Username";
	
	//Database names and columns for user table 
	public static final String TABLE_MESSAGE = "Message";
	public static final String MESSAGE_ID = "Id";
	public static final String CLOUD_MESSAGE_ID = "MessageId";
	public static final String FROM_USER_ID = "FromCloudId";
	public static final String FROM_USERNAME = "FromUsername";
	public static final String TO_USER_ID = "ToCloudId";
	public static final String TO_USERNAME = "ToUsername";
	public static final String MESSAGE = "Message";
	public static final String DATE_SENT = "DateSent";
	public static final String DATE_RECEIVED = "DateReveived";
	public static final String FROM_KEEP = "FromKep";
	public static final String TO_KEEP = "ToKeep";
	
	//query to create user table
	public static final String CREATE_DATABASE_USER = "" +
			"CREATE TABLE " + TABLE_USER + "("
				+ USER_ID + " integer primary key autoincrement,"
				+ CLOUD_ID + " integer NOT NULL,"
				+ USERNAME + " varchar(100) NOT NULL"
			+ ");";
	//query to create message table
	public static final String CREATE_DATABASE_MESSAGE = "" +
			"CREATE TABLE " + TABLE_MESSAGE + "("
				+ MESSAGE_ID + " integer primary key autoincrement,"
				+ CLOUD_MESSAGE_ID + " integer NOT NULL,"
				+ FROM_USER_ID + " integer NOT NULL,"
				+ FROM_USERNAME + " varchar(50) NOT NULL,"
				+ TO_USER_ID + " integer NOT NULL,"
				+ TO_USERNAME + " varchar(50) NOT NULL,"
				+ MESSAGE + " varchar(25) NOT NULL,"
				+ DATE_SENT + " datetime,"
				+ DATE_RECEIVED + "  datetime,"
				+ FROM_KEEP + " boolean NOT NULL,"
				+ TO_KEEP + " boolean NOT NULL"
			+ ");";
	
	
	private Context context;
	private DbHelper helper;
	private SQLiteDatabase database;
	
	private final class DbHelper extends SQLiteOpenHelper{

		public DbHelper(Context context) {
			super(context, DATABASE_NAME, null, SCHEMA_VERSION);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			db.execSQL(CREATE_DATABASE_MESSAGE);
			db.execSQL(CREATE_DATABASE_USER);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_MESSAGE);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
			onCreate(db);
		}
	}
	
	public Database(Context c){
		context = c;
	}
	
	public Database open(){
		helper= new DbHelper(context);
		database = helper.getWritableDatabase();
		return this;
	}
	
	public void close(){
		helper.close();
	}
	
	/*
	 * /////////////////////////////////
	 * IMPLEMENT DATABASE METHODS HERE
	 * /////////////////////////////////
	 */
	
	/*
	 * Checks to see if any user is currently logged in, if so return user, else return null
	 */
	public User CheckUserLoggedIn(){
		Cursor cursor = database.query(TABLE_USER, new String[] {USER_ID, CLOUD_ID, USERNAME}, null, null, null, null, null);
		cursor.moveToFirst();
		Date nowDate = new Date(System.currentTimeMillis());
		Date checkDate;
		User user = new User();
		if(cursor.getCount() == 0){
			cursor.close();
			database.close();
			return null;
		}
		else{
			user.Id = cursor.getInt(cursor.getColumnIndex(USER_ID));
			user.CloudId = cursor.getInt(cursor.getColumnIndex(CLOUD_ID));
			user.Username = cursor.getString(cursor.getColumnIndex(USERNAME));
			return user;
		}
	}
	
	//Deletes all messages, will be called when a new user is logged in
	public void DeleteAllMessages(){
		database.delete(TABLE_MESSAGE, null, null);
		database.close();
	}
	
	public void LogOutUser(){
		database.delete(TABLE_USER, null,null);
	}
	
	//Inserts a new logged in user to the database
	public void InsertNewCloudUser(int cloudId, String username){
		//remove previous user
		database.delete(TABLE_USER, null,null);
		ContentValues values = new ContentValues();
		values.put(CLOUD_ID, cloudId);
		values.put(USERNAME, username);

		// Inserting Row
		database.insert(TABLE_USER, null, values);
		database.close(); // Closing database connection
	}
	/**
	 * This methods gets all of the unique people with whom we have a conversation going.
	 * @return An ArrayList of contact objects, each object having the FromID and name.
	 */
	public ArrayList<Contact> getUniqueFromContacts()
	{
		ArrayList<Contact> fromContacts = new ArrayList<Contact>();
		String query = "SELECT DISTINCT " + FROM_USER_ID + ", " + FROM_USERNAME + ", " + TO_USER_ID + ", " + TO_USERNAME + " FROM " + TABLE_MESSAGE + " ORDER BY " + DATE_SENT + " ASC";
		Cursor cursor = database.rawQuery(query, null);
		if (cursor.moveToFirst())
		{
			do
			{
				String x = cursor.getString(0); 
				String y = cursor.getString(1);
				String x1 = cursor.getString(2); 
				String y1 = cursor.getString(3);
				fromContacts.add(
						new Contact(
								Integer.parseInt(x),
								y
								)
						);
				fromContacts.add(
						new Contact(
								Integer.parseInt(x1),
								y1
								)
						);
			} while (cursor.moveToNext());
		}
		cursor.close();
		return fromContacts;
	}
	
	/*
	 * Takes a message object and inserts it into the database.
	 */
	public void insertMessage(Message message)
	{
		ContentValues colValues = new ContentValues();
		colValues.put(CLOUD_MESSAGE_ID, message.CloudId);
		colValues.put(FROM_USER_ID, message.FromUserId);
		colValues.put(FROM_USERNAME, message.FromUserName);
		colValues.put(TO_USER_ID, message.ToUserId);
		colValues.put(TO_USERNAME, message.ToUserName);
		colValues.put(MESSAGE, message.Message);

		colValues.put(DATE_SENT, message.DateSend.getDate());
		//colValues.put(DATE_RECEIVED, message.DateReceived.toString());
		colValues.put(FROM_KEEP, message.FromKeep);
		colValues.put(TO_KEEP, message.ToKeep);
		database.insert(TABLE_MESSAGE, null, colValues);
	}
	
	public ArrayList<Message> GetMessagesWithUser(int myId, int theirId){
		ArrayList<Message> messages = new ArrayList<Message>();
		System.out.println("this runs");
		String selectQuery = "SELECT * FROM " + TABLE_MESSAGE
				+ " WHERE ((" + FROM_USER_ID + " = " + myId + " AND " + TO_USER_ID + " = " + theirId + ") OR (" + FROM_USER_ID + " = " + theirId + " AND " + TO_USER_ID + " = " + myId + ")) ORDER BY "+ DATE_SENT +" DESC";
		Cursor cursor = database.rawQuery(selectQuery, null);
		if (cursor.moveToFirst()) {
			do {
				Message message = new Message();
				message.CloudId = (Integer.parseInt(cursor.getString(1)));
				message.FromUserId = (Integer.parseInt(cursor.getString(2)));
				message.FromUserName =(cursor.getString(3));
				message.ToUserId = (Integer.parseInt((cursor.getString(4))));
				message.ToUserName = (cursor.getString(5));
				message.Message = (cursor.getString(6));
				message.DateSend = (new java.sql.Date(cursor.getLong(7)));
				messages.add(message);
			} while (cursor.moveToNext());
		}

		cursor.close();
		database.close();
		
		return messages;
	}
	
	public Message GetMessageByCloudMessageId(int id){
		Cursor cursor = database.query(TABLE_MESSAGE, new String[] {FROM_USER_ID, TO_USER_ID}, CLOUD_MESSAGE_ID+"="+id, null, null, null, null);
		cursor.moveToFirst();
		Date nowDate = new Date(System.currentTimeMillis());
		Date checkDate;
		Message message = new Message();
		if(cursor.getCount() == 0){
			cursor.close();
			database.close();
			return null;
		}
		else{
			message.FromUserId = cursor.getInt(cursor.getColumnIndex(FROM_USER_ID));
			message.ToUserId = cursor.getInt(cursor.getColumnIndex(TO_USER_ID));
			message.CloudId = id;
			return message;
		}
	}
	
	public int GetMessageCount(){
		String countQuery = "SELECT Count(*) FROM " + TABLE_MESSAGE + ";";
		Cursor cursor = database.rawQuery(countQuery, null);
		cursor.moveToFirst();
		if(cursor.getCount() == 0){
			cursor.close();
			database.close();
			return -1;
		}
		else{
			int total = cursor.getInt(0);
			cursor.close();
			database.close();
			return total;
		}
	}
	
	public ArrayList<Message> GetAllSavedMessages(int id){
		ArrayList<Message> messages = new ArrayList<Message>();
		String selectQuery = "SELECT * FROM " + TABLE_MESSAGE
				+ " WHERE ((" + FROM_USER_ID + " = " + id + " AND " + FROM_KEEP + " = 1) OR (" + TO_USER_ID + " = " + id + " AND " + TO_KEEP + " = 1)) ORDER BY "+ DATE_SENT +" DESC";
		Cursor cursor = database.rawQuery(selectQuery, null);
		if (cursor.moveToFirst()) {
			do {
				System.out.println("Running");
				Message message = new Message();
				message.CloudId = (Integer.parseInt(cursor.getString(1)));
				message.FromUserId = (Integer.parseInt(cursor.getString(2)));
				message.FromUserName =(cursor.getString(3));
				message.ToUserId = (Integer.parseInt((cursor.getString(4))));
				message.ToUserName = (cursor.getString(5));
				message.Message = (cursor.getString(6));
				message.DateSend = (new java.sql.Date(cursor.getLong(7)));
				messages.add(message);
			} while (cursor.moveToNext());
		}

		cursor.close();
		database.close();
		
		return messages;
	}
}
